import sys
sys.path.append("./")

from correction_pipeline import correction_pipeline
from utils.config_utils import read_file_line_by_line
from utils.db_utils import Database
from handwriting import recognize_handwriting_content
from yj_api import get_image_pipeline, fetch_school_list, fetch_school_data, get_chinese_subject_info
import pandas as pd
import random
import os
import time


# 拼接图片名称
def generate_image_name(exam_id, stu_id, student_name, score):
    return '{}_{}_{}_{}.png'.format(exam_id, stu_id, student_name, score)

# 解析图片名称
def parsed_image_name(image_name):
    # 先从路径中提取出文件名
    if not os.path.isabs(image_name):
        image_name = os.path.basename(image_name)
    return image_name.split('.')[0].split('_')


# 根据科目ID和学生考号选择一张图片作为测试用例
def choose_specific_image_to_correct(subject_id, kaohao):

    begin_time = time.time()
    res, save_path, img_url = get_image_pipeline(subject_id, kaohao)
    print('get_image_pipeline time: ', time.time() - begin_time)
    if not res or save_path is None:
        return None
    
    #test_image_path = os.path.join('./data', os.path.basename(save_path))
    test_image_path = save_path
    print('test_image_path: ', test_image_path)

    # 识别并批改
    begin_time = time.time()
    correction_result, recognized_word = correction_pipeline(test_image_path, config_file='./config.json')
    print('correction_pipeline time: ', time.time() - begin_time)

    # 返回组装的数据
    subject_id, kaohao, student_name, score = parsed_image_name(test_image_path)
    #base64_img = get_file_content_as_base64(test_image_path)
    base64_img = img_url

    result = {
        'subject_id': subject_id,
        'stu_id': kaohao,
        'student_name': student_name,
        'score': score,
        'correction_result': correction_result,
        'recognized_word': recognized_word,
        'base64_img': base64_img
    }

    return result


# 按照分数区间筛选出一个语文科目的学生作文数据
def search_students_by_score_interval(subject_id, score_lower=50, score_upper=60):
    try:

        school_list = fetch_school_list(subject_id)
        print(len(school_list), school_list)

        selected_students = []

        for school in school_list:
            #print('school: ', school)
            school_id = school.get('school_id')
            print(f"school_id: {school_id}")

            students = fetch_school_data(subject_id, school_id)
            #print("fetch_school_data students : ", students)
            for student in students:
                if student['essay_score'] >= score_lower and student['essay_score'] <= score_upper:
                    selected_students.append(student)

        return selected_students
    except Exception as e:
        print('Error: ', e)
        return []


# 根据考试ID列表查找对应的语文科目的列表
def search_chinese_subject_by_exam_id(exam_id_list):
    
    chinese_subject_list = []
    for exam_id in exam_id_list:
        chinese_subject_info = get_chinese_subject_info(exam_id)
        chinese_subject_list.append(chinese_subject_info)
        print(chinese_subject_info)

    return chinese_subject_list


def search_students_by_exam_ids(id_file = './examid.txt'):
    exam_id_list = read_file_line_by_line(id_file)
    chinese_subject_list = search_chinese_subject_by_exam_id(exam_id_list)
    #print(chinese_subject_list)
    student_list = []
    for i, chinese_subject in enumerate(chinese_subject_list):

        print('Processing {}/{}'.format(i+1, len(chinese_subject_list)))
        
        if chinese_subject is None:
            continue
        exam_id = chinese_subject.get('exam_id')
        subject_id = chinese_subject.get('subject_id')
        grade = chinese_subject.get('grade')

        students = search_students_by_score_interval(subject_id,score_lower=50, score_upper=60)
        print("search_students_by_score_interval : ", students)
        print(subject_id, len(students))
        for student in students:
            student['exam_id'] = exam_id
            student['grade'] = grade
            student_list.append(student)
    return student_list


# 定义一个作文数据类
class Essay:
    school_id:int
    exam_id:int
    subject_id:int
    template_id:int
    school_name:str
    # 学生考号
    kaohao:str
    # 学生姓名
    name:str
    # 学生年级
    grade:str
    # 图片的key
    image_key:str
    # 作文分数
    score:float
    # 过滤后的作文内容
    content:str
    # AI评分及批改
    ai_score:str


if __name__ == "__main__":
    print('test')

    db = Database('essay.db')

    # 动态创建表
    db.create_table_from_class(Essay)


    student_list = search_students_by_exam_ids(id_file = './examid.txt')
    print(len(student_list))
    print(student_list)

    # 遍历每个学生并进行识别及入库
    for i, student in enumerate(student_list):
        try:
            print('Processing {}/{}'.format(i+1, len(student_list)))
            print('processing kaohao : ', student.get('student_kaohao'))
            if i < 6742:
                continue

            school_id = student.get('school_id')
            exam_id = student.get('exam_id')
            subject_id = student.get('subject_id')
            template_id = student.get('templateid')
            school_name = student.get('school_name')
            kaohao = student.get('student_kaohao')
            student_name = student.get('student_name')
            grade = student.get('grade')
            score = student.get('essay_score')
            content = ''

            res, full_save_path, img_key = get_image_pipeline(subject_id, kaohao)

            #识别作文
            if res:
                print('recognizing...')
                parsed_text = recognize_handwriting_content(full_save_path)
                content = parsed_text

                print('inserting...')
                essay = Essay()
                essay.school_id = school_id
                essay.exam_id = exam_id
                essay.subject_id = subject_id
                essay.template_id = template_id
                essay.school_name = school_name
                essay.kaohao = kaohao
                essay.name = student_name
                essay.grade = grade
                essay.score = score
                essay.content = content
                essay.ai_score = ''
                essay.image_key = ''
                db.insert_from_object(essay)

                print("write to md file:")
                #with open('./essays.md', 'a+', encoding='utf-8') as f:
                with open('D:\\me\\logseq\\pages\\高分学生作文13.md', 'a+', encoding='utf-8') as f:
                    f.write(f"## {exam_id}-{subject_id}-{kaohao}-{student_name}-{grade}-{score}\n\n")
                    f.write(content + "\n\n")
                    f.close()

            else:
                print('Error: get image failed')
                continue
        except Exception as e:
            print("exception")
            continue


    table_name = 'Essay'
    table_length = db.get_table_length(table_name)
    if table_length is not None:
        print(f"The number of rows in the table '{table_name}' is: {table_length}")



    # # 插入数据
    # sample_essay = Essay()
    # sample_essay.school_id = 28484
    # sample_essay.exam_id = 2114680
    # sample_essay.subject_id = 8414155
    # sample_essay.template_id = 0
    # sample_essay.school_name = '江苏省扬中高级中学'
    # sample_essay.kaohao = '215302015'
    # sample_essay.name = '余敦程'
    # sample_essay.grade = '高三'
    # sample_essay.score = 58.0
    # sample_essay.content = '这是一个测试的作文内容'
    # sample_essay.ai_score = '这是一个测试的AI评分'
    # db.insert_from_object(sample_essay)

    # # 更新数据
    # sample_essay.score = 54
    # db.update_from_object(sample_essay, {'id': 1})

    # # 查询数据
    # users = db.select('Essay')
    # print(users)

    # # 删除数据
    # db.delete('Essay', {'id': 1})

    # 查询数据
    # essays = db.select('Essay')
    # print(essays)